package study.top.mydb;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import study.top.common.DBHandler;
import study.top.common.DateTime;
import study.top.jsp.PageHandler;
import study.top.mydb.items.Category1Item;
import study.top.mydb.items.CategoryAllItem;

public class Category1 {
	static Logger logger = Logger.getLogger(Category1.class);
	
	
	private static final String TABLE = "category1";
	
	String datetime = DateTime.getInstance().getNowDateTimeString();
	
	// 데이터베이스 제어 객체에 대한 참조
	DBHandler db = null;

	public Category1() {
		// DB 객체에 대한 참조 연결
		db = DBHandler.getInstance();
	}
	
	/**
	 * Category1을 추가하는 메소드
	 * @param c1_name Category1 이름
	 * @return int
	 */
	public int add(String c1_name){
		logger.debug("=========method start >> Category1Add");
		logger.debug("[PARAMS] c1_name = " + c1_name);
		
		int seq = -1;

		// SQL ������ ���
		c1_name = c1_name.replace("'", "\'");

		// -----------------------------------------
		// ���� ����
		// -----------------------------------------
		// ���̺��� �ڵ� ������ �ƴϹǷ�,
		// �ִ밪 + 1 ������� �޼��� �ȿ��� ���Ѵ�.
		int c1no = db.getMax(TABLE, "seq", null) + 1;

		logger.debug("�ڵ� ��� ī�װ?1 ��ȣ : " + c1no);

		// Ʈ�����ǿ� ���� �Ǵ��� �� ���� �ʴ´ٸ�, ������ �ϸ� �ȴ�.
		db.beginTrans();

		// INSERT ó���� ���� ����
		String sql = "INSERT INTO category1("
				+ "seq, c1_name, reg_date, edit_date"
				+ ") VALUES ("+"%d, '%s','%s', '%s')";

		boolean result = db.execSql(sql, c1no, c1_name, datetime, datetime);

		// -----------------------------------------
		// ���ó��
		// -----------------------------------------
		if (result) {
			seq = c1no;
			db.commit();
			logger.debug("ó����� >> " + seq + "�� ī�װ?1 ��� ����");
		} else {
			db.rollback();
			logger.error("ó����� >> ������ �߰� ����");
		}
		
		
		logger.debug("=========method end >> Category1Add");
		return seq;
	}
	
	/**
	 * Category1을 수정하는 메소드
	 * @param seq 수정할 seq
	 * @param c1_name 수정할 카테고리1 이름
	 * @return boolean
	 */
	public boolean edit(int seq, String c1_name){
		logger.debug("=========method start >> Category1Edit");
		logger.debug("[PARAMS] seq = " + seq);
		logger.debug("[PARAMS] c1_name = " + c1_name);
		
		// SQL ������ ���
		c1_name = c1_name.replace("'", "\'");

		boolean result = false;

		db.beginTrans();

		String sql = "UPDATE category1 SET c1_name = '%s', edit_date = '%s' WHERE seq = %d;";

		result = db.execSql(sql, c1_name, datetime, seq);

		if (result) {
			db.commit();
			logger.debug("ó����� >> " + seq + "�� ī�װ?1 ��������");
		} else {
			db.rollback();
			logger.error("ó����� >> ������ ���� ���� ");
		}
		
		logger.debug("=========method end >> Category1Edit");
		return result;
	}
	
	/**
	 * 카테고리1을 삭제하는 메소드
	 * @param seq 삭제할 seq
	 * @return boolean
	 */
	public boolean delete(int seq){
		logger.debug("=========method start >> Category1delete");
		logger.debug("[PARAMS] seq = " + seq);

		boolean result = false;

		db.beginTrans();

		String sql = "update category1 set reg_date = 0 where seq = %d";
		result = db.execSql(sql, seq);

		if (result) {
			db.commit();
			logger.debug("삭제 성공 >> " + seq + "번 카테고리 삭제완료.");
		} else {
			db.rollback();
			logger.error("삭제 실패  >> 삭제되지 않았습니다. ");
		}
		
		logger.debug("=========method end >> Category1delete");
		return result;
	}
	
	/**
	 * 카테고리1을 보여주는 메소드
	 * @param seq 카테고리1 seq
	 * @return Category1Item
	 */
	public Category1Item show(int seq){
		logger.debug("=========method start >> Category1show");
		logger.debug("[PARAMS] seq = " + seq);
		Category1Item item = null;
		
		String sql = "SELECT\n" +
				"	seq,\n" +
				"	c1_name,\n" +
				"	reg_date,\n" +
				"	edit_date\n" +
				"FROM\n" +
				"	category1\n" +
				"WHERE\n" +
				"	seq = %d";
		ResultSet rs = db.getResult(sql, seq);
		logger.debug("[rs] rs = " + rs);
		if(rs != null) {
			// ������ ��ȸ ��� �ִ� ���
			try {
				rs.next();
				
				// 1) ResultSet�� ��� �����͸� ������ ����
				int c1no = rs.getInt(1);
				String c1_name = rs.getString(2);
				String reg_date = rs.getString(3);
				String edit_date = rs.getString(4);
				
				logger.debug("c1no : " + c1no);
				logger.debug("c1_name : " + c1_name);
				logger.debug("reg_date : " + reg_date);
				logger.debug("edit_date : " + edit_date);
				
				// 2) ����� ���� ��ü�� ����
				item = new Category1Item(c1no, c1_name, reg_date, edit_date);
				
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 3) ������ ��ȸ�� ���� �޸� �ݳ�
				try {
					rs.close();
				} catch (SQLException e) {}
				rs = null;
			}
		}
		
		if(item == null) {
			logger.error("������ ��ȸ ����");
		} else {
			logger.debug("������ ��ȸ ����");
			logger.debug(item.toString());
		}
		
		logger.debug("=========method end >> Category1show");
		return item;
	}
	
	/**
	 * 카테고리1 리스트를 보여주는 메소드
	 * @return ArrayList<Category1Item>
	 */
	public ArrayList<Category1Item> selectCategory1List(){
		logger.debug("=========method end >> selectCategory1List");
		ArrayList<Category1Item> list = null;
		
		// ������ ��ȸ
		String sql = "SELECT\n" +
				"	seq,\n" +
				"	c1_name,\n" +
				"	reg_date,\n" +
				"	edit_date\n" +
				"	FROM\n" +
				"	category1\n" +
				"	WHERE reg_date > 0";

		ResultSet rs = db.getResult(sql);

		if (rs != null) {
			// ������ ��ȸ ��� �ִ� ���
			try {
				list = new ArrayList<Category1Item>();

				while (rs.next()) {

					// 1) ResultSet�� ��� �����͸� ������ ����
					int c1no = rs.getInt(1);
					String c1_name = rs.getString(2);
					String reg_date = rs.getString(3);
					String edit_date = rs.getString(4);

					// 2) ����� ���� ��ü�� ����
					Category1Item item = new Category1Item(c1no, c1_name, reg_date, edit_date);

					// 3) ArrayList�� �߰�
					list.add(item);
				}
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 4) ������ ��ȸ�� ���� �޸� �ݳ�
				try {
					rs.close();
				} catch (SQLException e) {
				}
				rs = null;
			}
		}

		if (list == null) {
			logger.error("������ ��ȸ ����");
		} else {
			logger.debug("������ ��ȸ ���� >> " + list.size() + "�� ��ȸ��");
		}
		
		logger.debug("=========method end >> selectCategory1List");
		return list;
	}
	
	/**
	 * category1,2 List, Pagging method
	 * @param page // now page number
	 * @param showPage // show in a list page count
	 * @param pageNumber // under the page number count
	 * @return
	 */
	public ArrayList<Category1Item> Category1List(int page, int showPage, int pageNumber){
		logger.debug("=========method start >> Category1List");
		ArrayList<Category1Item> list = null;
		
		String count_table = "(";
		count_table += "select seq ";
		count_table += " from category1 ";
		count_table += " WHERE reg_date > 0 ";
		//count_table += " where ca1.seq = '1' ";
		//count_table += " group by ca1.seq ";
		count_table += ") AS tbl";

		//전체 글 갯수 조회
		int count = db.getCount(count_table, "*", null);
		
		if(count < 1){
			return null;
		}
		
		// 페이징 처리 --> 한 페이지에 8개씩, 페이지번호는 5개 출력
		PageHandler p = PageHandler.getInstance(page, count, showPage, pageNumber);
		
		// 글 목록 조회
		String sql = "SELECT ";
		sql += "seq, c1_name, reg_date, edit_date ";
		sql += "from category1 ";
		sql += "WHERE reg_date > 0 ";
		sql += "LIMIT %d, %d";
		
		ResultSet rs = db.getResult(sql, p.getStartRec(), p.getPageArticleCount());
		
		if(rs == null){
			return null;
		}
		
		list = new ArrayList<Category1Item>();
		
		try{
			while(rs.next()){
				int c1no = rs.getInt(1);
				String c1_name = rs.getString(2);
				String reg_date = rs.getString(3);
				String edit_date = rs.getString(4);

				Category1Item item = new Category1Item(c1no, c1_name, reg_date, edit_date);

				list.add(item);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try{
				rs.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			rs = null;
		}
		
		if (list == null) {
			logger.error("List is NULL");
		} else {
			logger.debug("List Size is >> " + list.size() + "...");
		}
		
		logger.debug("=========method end >> Category1List");
		return list;
	}
}
